package jxl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class CreateDataDic {

	public static void main(String[] args) {
		CreateDataDic obj = new CreateDataDic();
		File file = new File("D:\\打版\\aaa\\data.xls");
		ArrayList<ArrayList> lists = obj.readExcel(file);
		File file2 = new File("D:\\打版\\aaa\\table.xls");
		Map<String, String> tmap = obj.readTableExcel(file2);
		System.out.println(tmap.size());
		Map<String, ArrayList<ArrayList>> MyMap = obj.CreateSheetMap(tmap, lists);
		for (String s : MyMap.keySet()) {
			System.out.println(s + "---" + MyMap.get(s));
		}
		obj.WriteExcel(tmap, MyMap);
	}

	public void WriteExcel(Map<String, String> tMap, Map<String, ArrayList<ArrayList>> MyMap) {
		WritableWorkbook book = null;
		try {
			book = Workbook.createWorkbook(new File("D:\\打版\\aaa\\ftable2.xls"));
			WritableSheet sheet2 = book.createSheet("目录", 0);
			int i2 = 1;
			for (String s : MyMap.keySet()) {// 每个sheet数据，每张表
				System.out.println("aaaaaaaa>>>>>>>>" + s);
				String[] ss2 = s.split("\\.");
				sheet2.addCell(new Label(2, i2, ss2[0]));
				sheet2.addCell(new Label(3, i2, ss2[1]));
				sheet2.addCell(new Label(4, i2, tMap.get(s)));
				WritableSheet sheet = book.createSheet(s, i2);
				sheet.setColumnView(0, 35);
				sheet.setColumnView(1, 35);
				sheet.setColumnView(2, 20);
				sheet.setColumnView(3, 8);
				sheet.setColumnView(4, 35);
				sheet.setColumnView(5, 8);
				sheet.setColumnView(6, 8);
				sheet.setColumnView(7, 8);
				sheet.setColumnView(8, 8);
				sheet.setColumnView(9, 8);
				sheet.setColumnView(10, 8);
				sheet.setColumnView(11, 8);
				sheet.setRowView(1, 500);
				// 字体样式
				WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false,
						UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
				// 将定义好的单元格添加到工作表中
				WritableCellFormat wcf = new WritableCellFormat(wf);
				wcf.setBackground(jxl.format.Colour.YELLOW2);
				wcf.setAlignment(jxl.format.Alignment.CENTRE);
				wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
				wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
				// 写入数据并关闭文件
				String[][] ss = { { "表名", s, "", "", "", "", "", "", "", "", "", "返回" }, { "物理列名", "逻辑列名", "数据类型",
						"业务含义", "业务描述/业务规则", "空值标识", "主键", "外键", "取值范围/默认值", "关联关系", "变更类型", "备注" } };
				for (int i = 0; i < ss.length; i++) {
					for (int j = 0; j < ss[i].length; j++) {
						sheet.addCell(new Label(j, i, ss[i][j], wcf));
					}
				}
				sheet.addHyperlink(new WritableHyperlink(11, 0, "返回", sheet2, 0, 0));
				ArrayList<ArrayList> al = MyMap.get(s);
				for (int j = 0; j < al.size(); j++) {// 每行数据
					ArrayList a = al.get(j);
					for (int k = 0; k < a.size(); k++) {// 每列数据
						Label label = new Label(k, j + 2, a.get(k).toString());
						// 将定义好的单元格添加到工作表中
						sheet.addCell(label);
					}
				}
				sheet2.addHyperlink(new WritableHyperlink(3, i2++, ss2[1], sheet, 1, 0));
			}
			book.write();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (book != null) {
				try {
					book.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

	public Map<String, ArrayList<ArrayList>> CreateSheetMap(Map<String, String> tmap, ArrayList<ArrayList> lists) {
		Map<String, ArrayList<ArrayList>> map = new HashMap<String, ArrayList<ArrayList>>();
		for (String s : tmap.keySet()) {
			ArrayList<ArrayList> oneLists = new ArrayList<ArrayList>();
			for (ArrayList l : lists) {
				if (s.equals(l.get(0).toString())) {
					l.remove(0);
					oneLists.add(l);
				}
			}
			map.put(s, oneLists);
		}
		return map;
	}

	// 读取表信息
	public HashMap<String, String> readTableExcel(File file) {
		HashMap<String, String> hm = new HashMap<String, String>();
		InputStream is = null;
		try {
			// 创建输入流，读取Excel
			is = new FileInputStream(file.getAbsolutePath());
			// jxl提供的Workbook类
			Workbook wb = Workbook.getWorkbook(is);
			Sheet sheet = wb.getSheet(1);
			// sheet.getRows()返回该页的总行数
			for (int i = 1; i < sheet.getRows(); i++) {
				String owner = sheet.getCell(2, i).getContents();
				String table = sheet.getCell(3, i).getContents();
				String tableName = sheet.getCell(4, i).getContents();
				if (table != null && !"".equals(table))
					hm.put(owner + "." + table, tableName);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return hm;

	}

	// 读取字段信息
	public ArrayList readExcel(File file) {
		ArrayList lists = new ArrayList();
		InputStream is = null;
		try {
			// 创建输入流，读取Excel
			is = new FileInputStream(file.getAbsolutePath());
			// jxl提供的Workbook类
			Workbook wb = Workbook.getWorkbook(is);
			Sheet sheet = wb.getSheet(0);
			// sheet.getRows()返回该页的总行数
			for (int i = 0; i < sheet.getRows(); i++) {
				List list = new ArrayList();
				// sheet.getColumns()返回该页的总列数
				for (int j = 0; j < sheet.getColumns(); j++) {
					String cellinfo = sheet.getCell(j, i).getContents();
					list.add(cellinfo);
				}
				lists.add(list);
				// System.out.println("11" + list);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return lists;

	}
}
